﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;

namespace Cashier_Upgrade
{
    public class SQLiteUpgrade
    {
        /// <summary>
        /// 获取连接
        /// </summary>
        /// <returns></returns>
        private SQLiteConnection GetSQLiteConnection()
        {
            SQLiteConnection conn = null;
            try
            {
                String dbPath = "Data Source=" + System.Windows.Forms.Application.StartupPath + @"\db\cashier.s3db;Pooling=true;";
                conn = new SQLiteConnection(dbPath);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return conn;
        }

        /// <summary>
        /// 批量执行SQL
        /// </summary>
        /// <param name="sqlList"></param>
        public int BatchExecuteSQL(List<String> sqlList)
        {
            int count = 0;
            if (sqlList != null && sqlList.Count > 0)
            {
                using (SQLiteConnection conn = this.GetSQLiteConnection())
                {
                    SQLiteTransaction trans = null;
                    try
                    {
                        conn.Open();
                        trans = conn.BeginTransaction();
                        SQLiteCommand cmd = new SQLiteCommand(conn);
                        foreach (String sql in sqlList)
                        {
                            try
                            {
                                cmd.CommandText = sql;
                                cmd.ExecuteNonQuery();
                                trans.Commit();
                                count += 1;
                            }
                            catch
                            {
                                count += 1;
                            }
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        if (trans != null)
                        {
                            trans.Rollback();
                        }
                        throw ex;
                    }
                    finally
                    {
                        if (trans != null)
                        {
                            trans = null;
                        }
                    }
                }
            }
            return count;
        }

        /// <summary>
        /// 批量执行SQL
        /// </summary>
        /// <param name="sqlList"></param>
        public int DoWhileBatchExecuteSQL(List<String> sqlList)
        {
            int count = 0;
            if (sqlList != null && sqlList.Count > 0)
            {
                foreach (String sql in sqlList)
                {
                    using (SQLiteConnection conn = this.GetSQLiteConnection())
                    {
                        bool isCount = false;
                        SQLiteTransaction trans = null;
                        try
                        { 
                            conn.Open();
                            trans = conn.BeginTransaction();
                            SQLiteCommand cmd = new SQLiteCommand(conn);
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            trans.Commit();
                            isCount = true;
                            count += 1;
                        }
                        catch (Exception ex)
                        {
                            if (trans != null)
                            {
                                trans.Rollback();
                            }
                            if (!isCount)
                            {
                                count += 1;
                            }
                        }
                        finally
                        {
                            if (trans != null)
                            {
                                trans = null;
                            }
                        }
                    }
                }
            }
            return count;
        }
    }
}
